# scan_columns.py
# usage: 提取Excel中标记字段，查DB取值，生成并更新JSON参数文件。
# python ./src/tools/scan_columns.py

import os
import re
import json
import pandas as pd
import sqlite3
from openpyxl import load_workbook

# 配置路径
EXCEL_FILE = './data/表结构.xlsx'  # 修改为你的Excel文件路径
DB_PATH = './data/data.db'
OUTPUT_JSON = './data/parameters.txt'

# 确保 data 目录存在
os.makedirs('./data', exist_ok=True)

def extract_parameter(desc):
    """
    从字段描述中提取 * 和 ( 之间的内容作为 parameter
    例如：*地区名称(...) -> 地区名称
    """
    match = re.match(r'\*(.*?)\(', desc.strip())
    if match:
        return match.group(1).strip()
    return None

def get_values_from_db(conn, table_name, column_name):
    """
    从 SQLite 数据库中查询指定表的指定字段的所有唯一值
    """
    try:
        query = f'SELECT DISTINCT "{column_name}" FROM "{table_name}" WHERE "{column_name}" IS NOT NULL ORDER BY "{column_name}"'
        df = pd.read_sql_query(query, conn)
        return df.iloc[:, 0].dropna().tolist()  # 转为 list
    except Exception as e:
        print(f"查询错误: 表={table_name}, 字段={column_name}, 错误={e}")
        return []

def read_excel_with_merged_cells(filename):
    """
    使用 openpyxl 手动处理合并单元格，返回完整数据列表
    """
    workbook = load_workbook(filename)
    sheet = workbook.active

    data = []
    current_table = None
    current_table_desc = None

    # 获取所有合并单元格范围
    merged_cells = sheet.merged_cells.ranges

    def get_unmerged_value(cell):
        """获取单元格真实值，处理合并单元格"""
        for mcr in merged_cells:
            if cell.coordinate in mcr:
                return mcr.start_cell.value
        return cell.value

    # 逐行读取
    for row in sheet.iter_rows(min_row=2):  # 假设第一行是标题
        raw_values = [get_unmerged_value(cell) for cell in row]

        # 解包列：序号、表名、表描述、字段名、字段类型、字段描述
        # 注意：由于表名列可能为空（因合并），需判断是否需要更新 current_table
        serial_no = raw_values[0]
        table_candidate = raw_values[1]
        table_desc_candidate = raw_values[2]
        field_name = raw_values[3]
        field_type = raw_values[4]
        field_desc = raw_values[5]

        # 更新当前表名和表描述（如果非空）
        if table_candidate is not None:
            current_table = table_candidate
        if table_desc_candidate is not None:
            current_table_desc = table_desc_candidate

        # 只保留有字段名的有效行
        if field_name and field_type and field_desc:
            data.append({
                'table_name': current_table,
                'table_desc': current_table_desc,
                'field_name': field_name,
                'field_type': field_type,
                'field_desc': str(field_desc) if field_desc else ""
            })

    return data

def main():
    # 读取 Excel
    print("正在读取 Excel 文件...")
    try:
        rows = read_excel_with_merged_cells(EXCEL_FILE)
    except Exception as e:
        print(f"读取 Excel 失败: {e}")
        return

    # 连接数据库
    print("连接数据库...")
    try:
        conn = sqlite3.connect(DB_PATH)
    except Exception as e:
        print(f"无法连接到数据库 {DB_PATH}: {e}")
        return

    # 加载已有的 parameters.json，避免重复
    parameters_dict = {}
    if os.path.exists(OUTPUT_JSON):
        try:
            with open(OUTPUT_JSON, 'r', encoding='utf-8') as f:
                existing_list = json.load(f)
                for item in existing_list:
                    parameters_dict[item['parameter']] = item['values']
        except Exception as e:
            print(f"读取已有 parameters.txt 失败: {e}")

    # 遍历每一行
    updated_records = []
    for row in rows:
        field_desc = row['field_desc'].strip()

        if field_desc.startswith('*'):
            parameter = extract_parameter(field_desc)
            if not parameter:
                print(f"跳过无法解析的字段描述: {field_desc}")
                continue

            table_name = row['table_name']
            column_name = row['field_name']

            print(f"处理参数: {parameter} (表: {table_name}, 字段: {column_name})")

            # 查询数据库获取 values
            values = get_values_from_db(conn, table_name, column_name)

            # 更新逻辑：如果 parameter 已存在，合并 values；否则新增
            if parameter in parameters_dict:
                # 去重合并
                new_values = list(set(parameters_dict[parameter] + values))
                # 去重并转为字符串排序
                parameters_dict[parameter] = sorted(set(str(v) for v in new_values if v is not None))
                # parameters_dict[parameter] = sorted(new_values)  # 可选排序
            else:
                parameters_dict[parameter] = sorted(values) if values else []

    # 转为列表格式保存
    output_list = [
        {"parameter": param, "values": vals}
        for param, vals in parameters_dict.items()
    ]

    # 写入 JSON 文件
    try:
        with open(OUTPUT_JSON, 'w', encoding='utf-8') as f:
            json.dump(output_list, f, ensure_ascii=False, indent=2)
        print(f"成功写入 {OUTPUT_JSON}")
    except Exception as e:
        print(f"写入文件失败: {e}")

    conn.close()
    print("扫描完成。")

if __name__ == '__main__':
    main()